Re: Composite types for composite primary/foreign keys?

Hello,

and thanks again for your reply.

And excuse me for taking so long to reply.

> I wanted to simplify the schema and make it more "readable" for
>> clueless morons like me. >;->
>
> Simplifying the schema is fine (and good!) as long as it exhibits the
> same behavior as the more complex one:

Well, that (same behaviour) is probably not the case in my case (see
below).

> often in the course of
> simplifying you find a solution yourself. However, we cannot help you
> if you don't provide adequate information.

I'm not sure whether I am violating some copyright, so I didn't want to
post the SQL script here. But the script is publicly downloadable at
www.mimosa.org, and I only need a part of it to explain the basic
concept. So this is the "complex" schema.

CREATE TABLE enterprise_type(
    ent_db_site         cris_string16_type      NOT NULL,
    ent_db_id           cris_uint_type          NOT NULL,
    ent_type_code       cris_uint_type          NOT NULL,
    name                cris_string254_type     NOT NULL,
    user_tag_ident      cris_string254_type,
    gmt_last_updated    cris_datetime_type,
    last_upd_db_site    cris_string16_type,
    last_upd_db_id      cris_uint_type,
    rstat_type_code     cris_ushort_type,
    PRIMARY KEY (ent_db_site, ent_db_id, ent_type_code)
)
;

CREATE TABLE enterprise(
    enterprise_id       cris_uint_type              NOT NULL,
    ent_db_site         cris_string16_type          NOT NULL,
    ent_db_id           cris_uint_type              NOT NULL,
    ent_type_code       cris_uint_type              NOT NULL,
    user_tag_ident      cris_string254_type,
    name                cris_string254_type         NOT NULL,
    gmt_last_updated    cris_datetime_type,
    last_upd_db_site    cris_string16_type,
    last_upd_db_id      cris_uint_type,
    rstat_type_code     cris_ushort_type,
    PRIMARY KEY (enterprise_id)
)
;

CREATE TABLE site_type(
    st_db_site          cris_string16_type      NOT NULL,
    st_db_id            cris_uint_type          NOT NULL,
    st_type_code        cris_uint_type          NOT NULL,
    name                cris_string254_type     NOT NULL,
    user_tag_ident      cris_string254_type,
    mobile_yn           cris_no_or_yes_type,
    gmt_last_updated    cris_datetime_type,
    last_upd_db_site    cris_string16_type,
    last_upd_db_id      cris_uint_type,
    rstat_type_code     cris_ushort_type,
    PRIMARY KEY (st_db_site, st_db_id, st_type_code)
)
;

CREATE TABLE site_type_child(
    st_db_site            cris_string16_type    NOT NULL,
    st_db_id              cris_uint_type        NOT NULL,
    st_type_code          cris_uint_type        NOT NULL,
    child_st_db_site      cris_string16_type    NOT NULL,
    child_st_db_id        cris_uint_type        NOT NULL,
    child_st_type_code    cris_uint_type        NOT NULL,
    gmt_last_updated    cris_datetime_type,
    last_upd_db_site    cris_string16_type,
    last_upd_db_id      cris_uint_type,
    rstat_type_code       cris_ushort_type,
    PRIMARY KEY (st_db_site, st_db_id, st_type_code, child_st_db_site,
child_st_db_id, child_st_type_code)
)
;

CREATE TABLE site(
    site_code           cris_string16_type      NOT NULL,
    enterprise_id       cris_uint_type          NOT NULL,
    site_id             cris_uint_type          NOT NULL,
    st_db_site          cris_string16_type      NOT NULL,
    st_db_id            cris_uint_type          NOT NULL,
    st_type_code        cris_uint_type          NOT NULL,
    user_tag_ident      cris_string254_type,
    name                cris_string254_type,
    duns_number         cris_uint_type,
    template_yn         cris_no_or_yes_type,
    gmt_last_updated    cris_datetime_type,
    last_upd_db_site    cris_string16_type,
    last_upd_db_id      cris_uint_type,
    rstat_type_code     cris_ushort_type,
    PRIMARY KEY (site_code)
)
;

CREATE TABLE manufacturer(
    mf_db_site            cris_string16_type    NOT NULL,
    mf_db_id              cris_uint_type        NOT NULL,
    manuf_code            cris_uint_type        NOT NULL,
    manuf_trade_name      cris_string254_type   NOT NULL,
    company_name          cris_string254_type   NOT NULL,
    phys_addr             cris_string254_type,
    phys_city_name        cris_string254_type,
    phys_state_abbr       cris_string254_type,
    phys_postal_code      cris_string254_type,
    phys_country_abbr     cris_string254_type,
    mail_addr             cris_string254_type,
    mail_city_name        cris_string254_type,
    mail_state_abbr       cris_string254_type,
    mail_postal_code      cris_string254_type,
    mail_country_abbr     cris_string254_type,
    us_ph_number          cris_string254_type,
    int_ph_country_no     cris_string254_type,
    int_ph_city_no        cris_string254_type,
    int_ph_local_no       cris_string254_type,
    us_fax_number         cris_string254_type,
    int_fax_country_no    cris_string254_type,
    int_fax_city_no       cris_string254_type,
    int_fax_local_no      cris_string254_type,
    business_desc         cris_string254_type,
    primary_sic           cris_string254_type,
    user_tag_ident        cris_string254_type,
    gmt_last_updated    cris_datetime_type,
    last_upd_db_site    cris_string16_type,
    last_upd_db_id      cris_uint_type,
    rstat_type_code       cris_ushort_type,
    lc_alt_sic1           cris_string254_type,
    lc_alt_sic2           cris_string254_type,
    PRIMARY KEY (mf_db_site, mf_db_id, manuf_code)
)
;

CREATE TABLE site_database(
    db_site             cris_string16_type      NOT NULL,
    db_id               cris_uint_type          NOT NULL,
    user_tag_ident      cris_string254_type,
    name                cris_string254_type,
    mf_db_site          cris_string16_type,
    mf_db_id            cris_uint_type,
    manuf_code          cris_uint_type,
    gmt_last_updated    cris_datetime_type,
    last_upd_db_site    cris_string16_type,
    last_upd_db_id      cris_uint_type,
    rstat_type_code     cris_ushort_type,
    PRIMARY KEY (db_site, db_id)
)
;

Now I wanted to make it more readable and separate the identification
schema from the actual data by defining composite *_key_type types for
each table like this:

CREATE TYPE enterprise_type_key_type AS (
    ent_db_key          site_database_key_type
    ent_type_code       cris_uint_type
)
;

CREATE TABLE enterprise_type(
    ent_type_key        enterprise_type_key_type    NOT NULL,
    name                cris_string254_type         NOT NULL,
    user_tag_ident      cris_string254_type,
    last_upd_data       cris_last_upd_data_type,
    rstat_type_key      row_status_type_key_type,
    PRIMARY KEY (ent_type_key)
)
;

CREATE TYPE enterprise_key_type AS (
    enterprise_id       cris_uint_type
)
;

CREATE TABLE enterprise(
    enterprise_key      enterprise_key_type         NOT NULL,
    ent_type_key        enterprise_type_key_type    NOT NULL,
    name                cris_string254_type         NOT NULL,
    user_tag_ident      cris_string254_type,
    last_upd_data       cris_last_upd_data_type,
    rstat_type_key      row_status_type_key_type,
    PRIMARY KEY (enterprise_key)
)
;

CREATE TYPE site_type_key_type AS (
    st_db_key           site_database_key_type,
    st_type_code        cris_uint_type
)
;

CREATE TABLE site_type(
    st_type_key         site_type_key_type      NOT NULL,
    name                cris_string254_type     NOT NULL,
    user_tag_ident      cris_string254_type,
    mobile_yn           cris_no_or_yes_type,
    last_upd_data       cris_last_upd_data_type,
    rstat_type_key      row_status_type_key_type,
    PRIMARY KEY (st_type_key)
)
;

CREATE TYPE site_type_child_key_type AS (
    st_type_key          site_type_key_type,
    child_st_type_key    site_type_key_type
)
;

CREATE TABLE site_type_child(
    site_type_child_key   site_type_child_key_type
    last_upd_data         cris_last_upd_data_type,
    rstat_type_key      row_status_type_key_type,
    PRIMARY KEY (site_type_child_key)
)
;

CREATE TYPE site_key_type AS (
    site_code           cris_string16_type,
)
;

CREATE TABLE site(
    site_key            site_key_type               NOT NULL,
    enterprise_key      enterprise_key_type         NOT NULL,
    site_id             cris_uint_type              NOT NULL,
    st_type_key         site_type_key_type          NOT NULL,
    user_tag_ident      cris_string254_type,
    name                cris_string254_type,
    duns_number         cris_uint_type,
    template_yn         cris_no_or_yes_type,
    last_upd_data       cris_last_upd_data_type,
    rstat_type_key      row_status_type_key_type,
    PRIMARY KEY (site_key)
)
;

CREATE TYPE manufacturer_key_type AS (
    mf_db_key             site_database_key_type,
    manuf_code            cris_uint_type
)
;

CREATE TABLE manufacturer(
    manuf_key             manufacturer_key_type     NOT NULL,
    manuf_trade_name      cris_string254_type       NOT NULL,
    company_name          cris_string254_type       NOT NULL,
    phys_addr             cris_addr_data_type,
    mail_addr             cris_addr_data_type,
    ph_number             cris_telecom_data_type,
    fax_number            cris_telecom_data_type,
    business_desc         cris_string254_type,
    primary_sic           cris_string254_type,
    user_tag_ident        cris_string254_type,
    last_upd_data         cris_last_upd_data_type,
    rstat_type_key      row_status_type_key_type,
    lc_alt_sic1           cris_string254_type,
    lc_alt_sic2           cris_string254_type,
    PRIMARY KEY (manuf_key)
)
;

CREATE TYPE site_database_key_type AS (
    db_site_key         site_key_type,
    db_id               cris_uint_type
)
;

CREATE TABLE site_database(
    db_key              site_database_key_type    NOT NULL,
    user_tag_ident      cris_string254_type,
    name                cris_string254_type,
    manuf_key           manufacturer_key_type,
    last_upd_data       cris_last_upd_data_type,
    rstat_type_key      row_status_type_key_type,
    PRIMARY KEY (db_key)
)
;

The objective was to make the table definitions more readable (less
fields) and to simplify the work in case the identification schema
changes.

Nonsense? Am I nuts? Is that in fact totally useless? Or is there a
better (simpler) way to achieve this?

TIA,

Sincerely,

Wolfgang Keller